package org.fastsyncer.manager.logmnr;

import java.io.UnsupportedEncodingException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.fastsyncer.common.entity.DataSourceConfig;
import org.fastsyncer.connector.util.DatabaseUtil;
import org.fastsyncer.manager.increment.logmnr.LogmnrConstants;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;

public class Logmnr {

	/** 上次数据同步最后SCN号 */
	public static String LAST_SCN = "0";

	/** 扫描所有日志 */
	public static boolean scanAll = false;

	private static JdbcTemplate jdbcTemplate = null;

	@Before
	public void init() {
		// 准备数据源
		String driverClassName = "oracle.jdbc.OracleDriver";
		String url = "jdbc:oracle:thin:@127.0.0.1:1521:TESTDB";
		String username = "sys as sysdba";
		String password = "Oracle123";
		username = "yangp as sysdba";
		password = "yangp";
		DataSourceConfig config = new DataSourceConfig(driverClassName, url, null, null, username, password);

		jdbcTemplate = DatabaseUtil.getJdbcTemplate(config);
		System.out.println("连接jdbc:" + jdbcTemplate + "\r");
	}

	@After
	public void destory() {
		try {
			jdbcTemplate.getDataSource().getConnection().close();
			System.out.println("关闭jdbc");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private static String loadLogs() throws SQLException {

		// 只查询正在运行的日志
		String sql = "SELECT MEMBER FROM V$LOGFILE";
		// 默认解析当前正在运行的日志
		if (!scanAll) {
			sql = "SELECT S.MEMBER FROM V$LOG L,V$LOGFILE S WHERE L.GROUP# = S.GROUP# AND L.STATUS = 'CURRENT'";
		}

		StringBuffer runningLogSQL = new StringBuffer();
		runningLogSQL.append("BEGIN");

		// 打印获分析日志文件信息
		SqlRowSet queryForRowSet = jdbcTemplate.queryForRowSet(sql);
		int i = 0;
		while (queryForRowSet.next()) {
			i++;
			if (i == 1) {
				runningLogSQL.append(" dbms_logmnr.add_logfile(logfilename=>'" + queryForRowSet.getObject(1)
						+ "', options=>dbms_logmnr.NEW);");
				continue;
			}
			runningLogSQL.append(" dbms_logmnr.add_logfile(logfilename=>'" + queryForRowSet.getObject(1)
					+ "', options=>dbms_logmnr.ADDFILE);");
		}
		runningLogSQL.append(" END;");
		return runningLogSQL + "";
	}

	private static void startLogmnr() throws SQLException {
		// 默认只查询正在运行的日志
		System.out.println("加载Logmnr分析日志");
		String loadLogs = loadLogs();
		System.out.println(loadLogs);
		jdbcTemplate.execute(loadLogs);

		// 启动解析器 StartScn=>'1326266',EndScn =>'1326536'
		String sql = "BEGIN dbms_logmnr.start_logmnr(startScn=>'0',dictfilename=>'D:\\Oracle\\logmnr\\dictionary.ora',OPTIONS =>DBMS_LOGMNR.COMMITTED_DATA_ONLY); END;";
		jdbcTemplate.execute(sql);
		System.out.println("启动Logmnr");
	}

	@Test
	public void parseLogs() throws SQLException {
		startLogmnr();

		String sql = "SELECT SCN,OPERATION,SQL_REDO FROM V$LOGMNR_CONTENTS T WHERE T.SEG_NAME='ASD_ABC'";
		SqlRowSet rs = jdbcTemplate.queryForRowSet(sql);
		Object scn = null;
		String opr = null;
		String sqlRedo = null;

		int columns = rs.getMetaData().getColumnCount();
		while (rs.next()) {
			scn = rs.getObject(1);
			// 过滤非INSER,UPDATE,DELETE日志
			opr = (String) rs.getObject(2);
			if (!opr.equalsIgnoreCase("INSERT") && !opr.equalsIgnoreCase("UPDATE") && !opr.equalsIgnoreCase("DELETE")) {
				continue;
			}

			System.out.println();
			for (int i = 1; i <= columns; i++) {
				System.out.print(rs.getObject(i) + "\t");
			}
			sqlRedo = (String) rs.getObject(3);
			System.out.println("\r" + sqlRedo);

			//解析日志
			try {
				JSONObject parseDML = parseDML(opr, sqlRedo);
				System.out.println("解析：" + parseDML + "\r");
			} catch (JSONException e) {
				e.printStackTrace();
			}
		}

		System.out.println("最后一次记录同步点：" + scn);
	}

	private JSONObject parseDML(String oper, String sql) throws JSONException {
		JSONObject json = null;
		switch (oper) {
		case LogmnrConstants.INSERT:
			json = insert(oper, sql);
			break;
		case LogmnrConstants.UPDATE:
			json = update(oper, sql);
			break;
		case LogmnrConstants.DELETE:
			json = delete(oper, sql);
			break;
		default:
			break;
		}
		return json;
	}

	private static JSONObject insert(String oper, String sql) throws JSONException {
		JSONObject insert = new JSONObject();
		JSONArray before = new JSONArray();
		JSONArray after = new JSONArray();

		// 截取列名字符串
		int startColIndex = sql.indexOf("(");
		int endColIndex = sql.indexOf(")");
		String sqlCol = sql.substring(startColIndex, endColIndex);
		// 找出列名
		String regex = "\"([\\s\\S]*?)\"";
		Pattern p = Pattern.compile(regex);
		Matcher m = p.matcher(sqlCol);
		while (m.find()) {
			String key = m.group().replaceAll("\"", "");
			JSONObject col = new JSONObject();
			col.put("name", key.replaceAll("\\s*", ""));
			col.put("value", "");
			after.put(col);
		}
		// 截取列值字符串
		String sqlVal = sql.substring(endColIndex, sql.length());
		sqlVal = sqlVal.substring(sqlVal.indexOf("(") + 1, sqlVal.lastIndexOf(")"));
		sqlVal = "SELECT " + sqlVal + " FROM DUAL";
		System.out.println(sqlVal);
		List<Object> vals = getValsFromSql(sqlVal);
		int aLen = after.length();
		for (int i = 0; i < aLen; i++) {
			JSONObject col = (JSONObject) after.get(i);
			Object val = vals.get(i);
			val = val==null?"":val;
			col.put("value", val);
		}
		insert.put("eventType", oper);
		insert.put("before", before);
		insert.put("after", after);
		return insert;
	}

	private static JSONObject update(String oper, String sql) throws JSONException {
		//模拟修改消息
		JSONObject update = new JSONObject();
		
		// 截取列名
		int startColIndex = sql.indexOf("set") + 3;
		int endColIndex = sql.indexOf("where");
		// 解析变化后的值和列名
		String sqlCol = sql.substring(startColIndex, endColIndex);
		JSONArray after = updateAfter(sqlCol);

		// 解析条件参数列名
		sqlCol = sql.substring(endColIndex + 5, sql.lastIndexOf(";"));
		JSONArray before = updateBefore(sqlCol);

		update.put("eventType", oper);
		update.put("before", before);
		update.put("after", after);
		return update;
	}

	private static JSONArray updateAfter(String sql) throws JSONException {
		JSONArray after = new JSONArray();
		
		List<String> keys = new ArrayList<>();
		String[] colArr = sql.split(", \"");
		StringBuilder sqlVal = new StringBuilder("SELECT ");
		int cLen = colArr.length;
		for (int i = 0; i < cLen; i++) {
			String[] attr = colArr[i].split("=");
			String key = attr[0].replaceAll("\"", "");
			keys.add(key);
			JSONObject col = new JSONObject();
			col.put("name", key.replaceAll("\\s*", ""));
			col.put("value","");
			after.put(col);
			
			String vStr = attr[1];
			sqlVal.append(vStr);
			if (i < cLen - 1) {
				sqlVal.append(",");
			}
		}
		sqlVal.append(" FROM DUAL");
		List<Object> vals = getValsFromSql(sqlVal + "");
		int aLen = after.length();
		for (int i = 0; i < aLen; i++) {
			JSONObject col = (JSONObject) after.get(i);
			Object val = vals.get(i);
			val = val==null?"":val;
			col.put("value", val);
		}
		return after;
	}

	private static JSONArray updateBefore(String sqlCol) throws JSONException {
		JSONArray before = new JSONArray();
		// 清洗表达式
		sqlCol = sqlCol.replaceAll("IS NULL", "= NULL");
		String[] colArr = sqlCol.split("and");
		int cLen = colArr.length;
		boolean isAppend = false;
		StringBuilder sqlVal = new StringBuilder("SELECT ");
		for (int i = 0; i < cLen; i++) {
			String[] attr = colArr[i].split("=");
			String key = attr[0].replaceAll("\"", "");
			JSONObject col = new JSONObject();
			col.put("name", key.replaceAll("\\s*", ""));
			col.put("value","");
			before.put(col);

			// 判断是否已经添加参数
			if (isAppend) {
				sqlVal.append(",");
			}
			sqlVal.append(attr[1]);
			isAppend = true;
		}
		sqlVal.append(" FROM DUAL");
		System.out.println(sqlVal);
		List<Object> vals = getValsFromSql(sqlVal + "");
		int bLen = before.length();
		for (int i = 0; i < bLen; i++) {
			JSONObject col = (JSONObject) before.get(i);
			Object val = vals.get(i);
			val = val==null?"":val;
			col.put("value", val);
		}
		return before;
	}

	private static JSONObject delete(String oper, String sql) throws JSONException {
		//模拟删除消息
		JSONObject delete = new JSONObject();
		JSONArray before = new JSONArray();
		
		String sqlCol = sql.substring(sql.indexOf("where") + 5, sql.length() - 1);
		// 找出列名
		String regex = "\"([\\s\\S]*?)\"";
		Pattern p = Pattern.compile(regex);
		Matcher m = p.matcher(sqlCol);
		while (m.find()) {
			String key = m.group().replaceAll("\"", "");
			JSONObject col = new JSONObject();
			col.put("name", key.replaceAll("\\s*", ""));
			col.put("value","");
			before.put(col);
		}

		// 清洗表达式
		sqlCol = sqlCol.replaceAll("IS NULL", "= NULL");
		// 找出值
		StringBuilder sqlVal = new StringBuilder("SELECT ");
		String[] colArr = sqlCol.split("and");
		int cLen = colArr.length;
		boolean isAppend = false;
		for (int i = 0; i < cLen; i++) {
			String[] attr = colArr[i].split("=");
			// 判断是否已经添加参数
			if (isAppend) {
				sqlVal.append(",");
			}
			sqlVal.append(attr[1]);
			isAppend = true;
		}
		sqlVal.append(" FROM DUAL");
		System.out.println(sqlVal);
		List<Object> vals = getValsFromSql(sqlVal + "");
		int bLen = before.length();
		for (int i = 0; i < bLen; i++) {
			JSONObject col = (JSONObject) before.get(i);
			Object val = vals.get(i);
			val = val==null?"":val;
			col.put("value", val);
		}
		
		delete.put("eventType", oper);
		delete.put("before", before);
		delete.put("after", new JSONArray());
		return delete;
	}
	
	private static List<Object> getValsFromSql(String sql) {
		List<Object> vals = new ArrayList<Object>();
		SqlRowSet rs = jdbcTemplate.queryForRowSet(sql);
		int columns = rs.getMetaData().getColumnCount();
		while (rs.next()) {
			for (int i = 1; i <= columns; i++) {
				Object obj = rs.getObject(i);
				if (obj instanceof oracle.sql.TIMESTAMP) {
					try {
						obj = oracle.sql.TIMESTAMP.toTimestamp(((oracle.sql.TIMESTAMP) obj).getBytes());
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				vals.add(obj);
			}
		}
		return vals;
	}

	@Test
	public void parseDMLTest() throws UnsupportedEncodingException, JSONException {

		String oper = "";
		String sql = "";

		// INSERT insert into "YANGP"."ASD_TEST"("ID","NAME","LAST_DATE") values
		// ('6',UNISTR('\6768'),TO_TIMESTAMP('13-6月 -17 09.41.40.216000 上午'));
//		oper = "INSERT";
//		sql = "insert into \"YANGP\".\"ASD_TEST\"(\"ID\",\"NAME\",\"LAST_DATE\") values ('6',UNISTR('\\6768'),TO_TIMESTAMP('13-6月 -17 09.41.40.216000 上午'));";
//		System.out.println(sql);
//		JSONObject insert = insert(oper, sql);
//		System.out.println("解析新增：" + insert + "\r");

		// UPDATE update "YANGP"."ASD_TEST" set "NAME" =
		// UNISTR('\5B59\4E03111'), "LAST_DATE" = TO_TIMESTAMP('13-6月 -17
		// 09.41.40.216000 上午') where "ID" = '5' and "NAME" =
		// UNISTR('\5B59\4E03') and "LAST_DATE" IS NULL;
		oper = "UPDATE";
		sql = "update \"YANGP\".\"ASD_TEST\" set \"NAME\" = UNISTR('\\5B59\\4E03111'), \"LAST_DATE\" = TO_TIMESTAMP('13-6月 -17 09.41.40.216000 上午') where \"ID\" = '5' and \"NAME\" = UNISTR('\\5B59\\4E03') and \"LAST_DATE\" IS NULL;	";
		sql = "update \"YANGP\".\"ASD_USER\" set \"NAME\" = '669' where \"ID\" = '6' and \"NAME\" = '66922' and ROWID = 'AAAR75AAEAAAAC3AAC';";
		System.out.println(sql);
		JSONObject update = update(oper, sql);
		System.out.println("解析修改：" + update + "\r");

		// DELETE delete from "YANGP"."ASD_TEST" where "ID" = '6' and "NAME" =
		// UNISTR('\6768666') and "LAST_DATE" = TO_TIMESTAMP('13-6月 -17
		// 09.41.40.216000 上午');
//		oper = "DELETE";
//		sql = "delete from \"YANGP\".\"ASD_TEST\" where \"ID\" = '6' and \"NAME\" = UNISTR('\6768666') and \"LAST_DATE\" = TO_TIMESTAMP('13-6月 -17 09.41.40.216000 上午');";
//		System.out.println(sql);
//		JSONObject delete = delete(oper, sql);
//		System.out.println("解析删除：" + delete + "\r");

	}
	
	@Test
	public void getLogMsg() throws JSONException {
		JSONObject t = new JSONObject();
		JSONArray msg = new JSONArray();
		JSONObject insert = testInsertMsg();
		JSONObject update = testUpdateMsg();
		JSONObject delete = testDeleteMsg();
		msg.put(insert);
		msg.put(update);
		msg.put(delete);
		
		t.put("taskName","USER_TEST");
		t.put("msg",msg);
		System.out.println(t);
	}
	
	public JSONObject testInsertMsg() throws JSONException{
		//模拟插入消息
		JSONObject insert = new JSONObject();
		JSONArray before = new JSONArray();
		JSONArray after = new JSONArray();
		
		JSONObject col1 = new JSONObject();
		col1.put("name","ID");
		col1.put("value","1000");
		JSONObject col2 = new JSONObject();
		col2.put("name","NAME");
		col2.put("value","张三");
		JSONObject col3 = new JSONObject();
		col3.put("name","LASTDATE");
		col3.put("value","2017-06-15");
		after.put(col1);
		after.put(col2);
		after.put(col3);
		
		insert.put("eventType", "INSERT");
		insert.put("before", before);
		insert.put("after", after);
		return insert;
	}
	
	public JSONObject testUpdateMsg() throws JSONException{
		//模拟修改消息
		JSONObject update = new JSONObject();
		JSONArray before = new JSONArray();
		JSONArray after = new JSONArray();
		
		//修改前
		JSONObject b1 = new JSONObject();
		b1.put("name","ID");
		b1.put("value","1000");
		JSONObject b2 = new JSONObject();
		b2.put("name","NAME");
		b2.put("value","张三");
		JSONObject b3 = new JSONObject();
		b3.put("name","LASTDATE");
		b3.put("value","2017-06-15");
		before.put(b1);
		before.put(b2);
		before.put(b3);
		
		//修改后
		JSONObject a1 = new JSONObject();
		a1.put("name","ID");
		a1.put("value","100011");
		JSONObject a2 = new JSONObject();
		a2.put("name","NAME");
		a2.put("value","张三111");
		JSONObject a3 = new JSONObject();
		a3.put("name","LASTDATE");
		a3.put("value","2017-06-15");
		after.put(a1);
		after.put(a2);
		after.put(a3);
		
		update.put("eventType", "UPDATE");
		update.put("before", before);
		update.put("after", after);
		return update;
	}
	
	public JSONObject testDeleteMsg() throws JSONException{
		//模拟删除消息
		JSONObject delete = new JSONObject();
		JSONArray before = new JSONArray();
		JSONArray after = new JSONArray();
		
		//删除前
		JSONObject b1 = new JSONObject();
		b1.put("name","ID");
		b1.put("value","1000");
		JSONObject b2 = new JSONObject();
		b2.put("name","NAME");
		b2.put("value","张三");
		JSONObject b3 = new JSONObject();
		b3.put("name","LASTDATE");
		b3.put("value","2017-06-15");
		before.put(b1);
		before.put(b2);
		before.put(b3);
		
		delete.put("eventType", "DELETE");
		delete.put("before", before);
		delete.put("after", after);
		return delete;
	}
	
	@Test
	public void trimSpace() {
		String ss = " def	f  w ";
		ss = ss.replaceAll("\\s*", "");
		System.out.println(ss);
	}
	
}
